use ggHD;
go 

create table ExternalInitiators(
	Id int not null identity,	
	FromAddress nvarchar(256) not null,
	FromDisplayText nvarchar(256) null,
	Added datetime default getdate(),
	)
go

create table TicketHeaders(
	Id bigint identity primary key,
	DateCreate datetime not null default getdate(),
	Header nvarchar(512) not null,
	InitiatorID int null, --reference to ExternalInitiators.Id
	CustomerID nvarchar(256) null, -- reference to Customers.ContractN
	RawMailId bigint not null foreign key references RawMails(Id)
	)
go

create table TiketsOfUsers(
	UserId int null foreign key references UserProfile(UserId),
	RoleId int null foreign key references webpages_Roles(RoleId),
	TicketId bigint not null foreign key references TicketHeaders(Id)
	)
go

create table TicketContent(
	Id bigint identity primary key,
	TicketHeaderId bigint not null foreign key references TicketHeaders(Id),
	TextContent nvarchar(max),
	DateCreate datetime not null default getdate()
	)
go
alter table TicketContent add RawMailId bigint null
go
alter table TicketContent add constraint FK_TicketCont_RawMails foreign key (RawMailId) references RawMails(Id)
go
alter table TicketContent add UserID int not null 
go
alter table TicketContent add constraint FK_TicketCont_UserProfile foreign key (UserId) references UserProfile(UserId)
go
--================== RawMails altering ======================--
alter table RawMails add MailAccount sysname null
go
alter table RawMails add MsgSubject nvarchar(512) null
go
alter table RawMails add MsgUid nvarchar(256) not null unique
go

/**************** settings ***********************/
create table SettingGroups(
	Id int identity not null primary key,
	Name sysname not null unique,
	Descr nvarchar(512) null
	)
go
create table Settings(
	Id int identity not null primary key,
	GroupId int null foreign key references SettingGroups(id),
	Name sysname not null,
	Value nvarchar(max) null
)
go
create table SettingValues(
	Id int identity primary key,
	SettingID int not null foreign key references Settings(Id),
	Value nvarchar(max) not null
)
/**************** settings ***********************/
